package com.dengcj.util;
import java.util.Map;

/**
 * @author dengchunjie
 * @Description: SQL组装工具类
 * @date 2022/1/11 09:47
 */
public class CjSqlUtil {

    /**
     * 组装insert sql
     * @param tablename 表名
     * @param colMap
     * @return
     */
    public static String getInsertSql(String tablename, Map<String,Object> colMap){
        if(colMap == null){
            return "";
        }

        StringBuffer sqlCol = new StringBuffer(),sqlVal = new StringBuffer();
        //然后通过循环遍历出的key值
        colMap.forEach((key,value) -> {
            if(value != null){
                sqlCol.append(","+key);
                sqlVal.append(",'"+value+"'");
            }
        });

        return "insert into " + tablename + "(" + sqlCol.substring(1) + ") values(" + sqlVal.substring(1) + ")";
    }

    /**
     * 组装insert sql: insert into tableA(a,b) select c,d from tableB
     * @param targetTableName 目标表名
     * @param sourceTableName 来源表名
     * @param colnameMap 字段对应关系
     * @param sqlWhere 筛选条件
     * @return
     */
    public static String getSelectInsertSql(String targetTableName, String sourceTableName, Map<String, String> colnameMap, String sqlWhere){
        if(colnameMap == null){
            return "";
        }

        StringBuffer targetColNameSb = new StringBuffer();
        StringBuffer sourceColNameSb = new StringBuffer();

        colnameMap.forEach((key,value) -> {
            targetColNameSb.append(","+key);
            sourceColNameSb.append(","+value);
        });

        return "insert into "+targetTableName+ "(" + targetColNameSb.substring(1) + ") select "+sourceColNameSb.substring(1) +" from "+sourceTableName+" "+sqlWhere;
    }

    /**
     * 组装update sql
     * @param tablename 表名
     * @param colMap
     * @param sqlwhere
     * @return
     */
    public static String getUpdateSql(String tablename,Map<String,Object> colMap,String sqlwhere){
        if(colMap == null || colMap.isEmpty()){
            return "";
        }

        StringBuffer sqlCol = new StringBuffer();
        colMap.forEach((key,value) ->
                sqlCol.append(","+key+"='"+value+"'")
        );

        return "update " + tablename + " set " + sqlCol.substring(1) + " "+sqlwhere;
    }

    /**
     * 组装update sql, 如果值为空则不更新
     * @param tablename 表名
     * @param colMap
     * @param sqlwhere
     * @return
     */
    public static String getUpdateSqlNotNull(String tablename,Map<String,Object> colMap,String sqlwhere){
        if(colMap == null || colMap.isEmpty()){
            return "";
        }

        StringBuffer sqlCol = new StringBuffer();
        colMap.forEach((key,value) ->{
            if(!"".equals(value)) {
                sqlCol.append("," + key + "='" + value + "'");
            }
        });

        return "".equals(sqlCol) ? "" : "update " + tablename + " set " + sqlCol.substring(1) + " "+sqlwhere;
    }
}
